#!/bin/ksh

LOGIN=$1
SH_DIR=$2
UP_DIR=$3
UP_FILE=$4
BUSINESS=$5

sqlplus -s $LOGIN<<!
DELETE FROM ccs.ccs_ams_tmp_abnormal_usage;
COMMIT;
!

cat > $UP_DIR/ctl/Upload_Abnormal.ctl<<!
LOAD DATA
INFILE '$UP_DIR/$UP_FILE'
APPEND INTO TABLE CCS.CCS_AMS_TMP_ABNORMAL_USAGE
TRAILING NULLCOLS
(
BUSINESS_TYPE            CONSTANT                                    '$BUSINESS',
ACCOUNT_NO               POSITION(001:008)     CHAR                  "TRIM(:ACCOUNT_NO)",
ACCOUNT_STATUS           POSITION(009:009)     CHAR                  "TRIM(:ACCOUNT_STATUS)",
MOBILE_NO                POSITION(010:017)     CHAR                  "TRIM(:MOBILE_NO)",
ACCOUNT_NAME             POSITION(018:047)     CHAR                  "TRIM(:ACCOUNT_NAME)",
CUSTOMER_CLASS           POSITION(048:052)     CHAR                  "TRIM(:CUSTOMER_CLASS)",
IDD                      POSITION(053:053)     CHAR                  "TRIM(:IDD)",
DDA                      POSITION(054:054)     CHAR                  "TRIM(:DDA)",
SALES_PLAN               POSITION(055:057)     CHAR                  "TRIM(:SALES_PLAN)",
MINUTE_USED              POSITION(058:061)     DECIMAL EXTERNAL      ,
DAY_CHARGE               POSITION(062:072)     DECIMAL EXTERNAL      ,
CURRENT_BALANCE          POSITION(073:083)     DECIMAL EXTERNAL      ,
CURRENT_BALANCE_SIGN     POSITION(084:084)     CHAR                  "TRIM(:CURRENT_BALANCE_SIGN)",
OVERDUE_BALANCE          POSITION(085:095)     DECIMAL EXTERNAL      ,
OVERDUE_BALANCE_SIGN     POSITION(096:096)     CHAR                  "TRIM(:OVERDUE_BALANCE_SIGN)",
FREQUENCY                POSITION(097:100)     DECIMAL EXTERNAL      ,
BILL_CYCLE               POSITION(101:102)     CHAR                  "TRIM(:BILL_CYCLE)",
AGENT_CODE               POSITION(103:110)     CHAR                  "TRIM(:AGENT_CODE)",
BEGIN_SERVICE_DATE       POSITION(111:118)     DATE(8)               "yyyymmdd"
)
!
cd $UP_DIR/
/usr/local/bin/dos2unix $UP_FILE
sqlldr $LOGIN \
control=$UP_DIR/ctl/Upload_Abnormal.ctl bad=$UP_DIR/bad/Upload_Abnormal.bad \
log=$UP_DIR/log/Upload_Abnormal.log >> $UP_DIR/log/Upload_Abnormal.log

